﻿using Mapster;
using MiniExcelLibs;
using Synergy.dbmodel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Synergy
{
    public partial class Form工序时间段报表 : Form
    {
        private BindingSource bs = new BindingSource();
        public Form工序时间段报表()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex == 0)
            {
                查正常报工();
            }
            else
            {
                查不良报工();
            }

        }

        private void 查正常报工()
        {
            string sql = $@"
                            DECLARE @TargetDate DATE = '{dateTimePicker1.Value.ToShortDateString()}';
                           select 时间段,sum([焊接]) 焊接,sum([保压/清洗])保压,sum([激光焊])激光焊,sum([装配])装配,
					sum([温度计制作])温度计制作,sum([真空充液])真空充液,sum([校表])校表,sum([加油/压盖/测漏/真空脱脂])加油,
					sum([检验])检验,sum([包装])包装 from (
                            SELECT 时间段,[焊接],[保压/清洗],[激光焊],[装配],[温度计制作],[真空充液],[校表],[加油/压盖/测漏/真空脱脂],
							[检验],[包装]
                            FROM
                            (
                            SELECT
   
                                -- 根据时间段分组
                                CASE
                                    WHEN DATEPART(HOUR, 报工时间) < 8 THEN '00:00-08:00'
                                    WHEN DATEPART(HOUR, 报工时间) >= 20 THEN '20:00-24:00'
                                    ELSE CONCAT(FORMAT(DATEPART(HOUR, 报工时间), '00'), ':00-', FORMAT(DATEPART(HOUR, 报工时间) + 1, '00'), ':00')
                                END AS 时间段,
	                             加工顺序,工序名称,
                                报工数量
                            FROM Z_baogong
                            WHERE 1=1 and CAST(报工时间 AS DATE) = @TargetDate ";
            sql = $@"
                    DECLARE @TargetDate DATE = '{dateTimePicker1.Value.ToShortDateString()}';
                           select 时间段,cast(sum([焊接]) as int) 焊接,cast(sum([保压/清洗]) as int)保压,
						   cast(sum([激光焊]) as int) 激光焊,cast(sum([装配])as int)装配,
						   cast(sum([温度计制作])as int) 温度计制作,cast(sum([真空充液])as int) 真空充液,
						   cast(sum([校表])as int)校表,cast(sum([加油/压盖/测漏/真空脱脂])as int)加油,
						   cast(sum([检验])as int) 检验,cast(sum([包装])as int) 包装 from (
                            SELECT 时间段,[焊接],[保压/清洗],[激光焊],[装配],[温度计制作],[真空充液],[校表],[加油/压盖/测漏/真空脱脂],
							[检验],[包装]
                            FROM
                            (
                            SELECT
   
                                -- 根据时间段分组
                                CASE
                                    WHEN DATEPART(HOUR, 报工时间) < 8 THEN '00:00-08:00'
                                    WHEN DATEPART(HOUR, 报工时间) >= 20 THEN '20:00-24:00'
                                    ELSE CONCAT(FORMAT(DATEPART(HOUR, 报工时间), '00'), ':00-', FORMAT(DATEPART(HOUR, 报工时间) + 1, '00'), ':00')
                                END AS 时间段,
	                             加工顺序,工序名称,
                                报工数量
                            FROM Z_baogong
                            WHERE 1=1 and CAST(报工时间 AS DATE) = @TargetDate ";
            if (string.IsNullOrEmpty(textBox1.Text.Trim()) == false)
            {
                sql += $" and 报工人员 like '%{textBox1.Text.Trim()}%'";
            }

            sql += @" ) AS SourceTable
                            PIVOT
                            (
                                SUM(报工数量)
                                FOR 工序名称 IN ([焊接],[保压/清洗],[激光焊],[装配],[温度计制作],[真空充液],[校表],[加油/压盖/测漏/真空脱脂],
							[检验],[包装])
                            ) AS PivotTable) t
							group by 时间段
                            ORDER BY 时间段;
                            ";

            List<Class工序时间段> cl = SqlSugarHelp.scope.Ado.SqlQuery<Class工序时间段>(sql);
            string[] arr = new string[] { "00:00-08:00", "08:00-09:00","09:00-10:00","10:00-11:00", "11:00-12:00", "12:00-13:00",
                "13:00-14:00","14:00-15:00","15:00-16:00"
                                        ,"16:00-17:00","17:00-18:00","18:00-19:00","19:00-20:00","20:00-24:00"};
            List<Class工序时间段> ora = new List<Class工序时间段>();
            for (int i = 0; i < arr.Length; i++)
            {
                Class工序时间段 c = new Class工序时间段 { 时间段 = arr[i] };
                ora.Add(c);
            }

            foreach (var item1 in cl)
            {
                var item2 = ora.FirstOrDefault(i2 => i2.时间段 == item1.时间段);
                if (item2 != null)
                {
                    // 使用Mapster映射除TimePeriod以外的所有字段
                    item1.Adapt(item2);
                    item2.小计 = item2.焊接 + item2.保压 + item2.加油 + item2.激光焊 + item2.装配 + item2.温度计制作 + item2.真空充液 + item2.校表
                        + item2.检验 + item2.包装;
                }
            }


            Class工序时间段 c2 = new Class工序时间段
            {
                时间段 = "汇总",
                焊接 = ora.Sum(it => it.焊接),
                保压 = ora.Sum(it => it.保压),
                加油 = ora.Sum(it => it.加油),
                激光焊 = ora.Sum(it => it.激光焊),
                装配 = ora.Sum(it => it.装配),
                温度计制作 = ora.Sum(it => it.温度计制作),
                真空充液 = ora.Sum(it => it.真空充液),
                校表 = ora.Sum(it => it.校表),
                检验 = ora.Sum(it => it.检验),
                包装 = ora.Sum(it => it.包装),
                小计 = ora.Sum(it => it.小计)
            };
            ora.Add(c2);

            //cl.Adapt(ora);
            dataGridView1.Columns.Clear();
            bs.DataSource = ora;
            dataGridView1.DataSource = bs;
            bs.ResetBindings(true);
        }

        private void 查不良报工()
        {
            string sql = $@"
                            DECLARE @TargetDate DATE = '{dateTimePicker1.Value.ToShortDateString()}';
                           select 时间段,sum([焊接]) 焊接,sum([保压/清洗])保压,sum([激光焊])激光焊,sum([装配])装配,
					sum([温度计制作])温度计制作,sum([真空充液])真空充液,sum([校表])校表,sum([加油/压盖/测漏/真空脱脂])加油,
					sum([检验])检验,sum([包装])包装 from (
                            SELECT 时间段,[焊接],[保压/清洗],[激光焊],[装配],[温度计制作],[真空充液],[校表],[加油/压盖/测漏/真空脱脂],
							[检验],[包装]
                            FROM
                            (
                            SELECT
   
                                -- 根据时间段分组
                                CASE
                                    WHEN DATEPART(HOUR, 报工时间) < 8 THEN '00:00-08:00'
                                    WHEN DATEPART(HOUR, 报工时间) >= 20 THEN '20:00-24:00'
                                    ELSE CONCAT(FORMAT(DATEPART(HOUR, 报工时间), '00'), ':00-', FORMAT(DATEPART(HOUR, 报工时间) + 1, '00'), ':00')
                                END AS 时间段,
	                             加工顺序,工序名称,
                                不合格数量
                            FROM Z_baogong
                            WHERE 1=1 and CAST(报工时间 AS DATE) = @TargetDate ";
            sql = $@"
                    DECLARE @TargetDate DATE = '{dateTimePicker1.Value.ToShortDateString()}';
                           select 时间段,cast(sum([焊接]) as int) 焊接,cast(sum([保压/清洗]) as int)保压,
						   cast(sum([激光焊]) as int) 激光焊,cast(sum([装配])as int)装配,
						   cast(sum([温度计制作])as int) 温度计制作,cast(sum([真空充液])as int) 真空充液,
						   cast(sum([校表])as int)校表,cast(sum([加油/压盖/测漏/真空脱脂])as int)加油,
						   cast(sum([检验])as int) 检验,cast(sum([包装])as int) 包装 from (
                            SELECT 时间段,[焊接],[保压/清洗],[激光焊],[装配],[温度计制作],[真空充液],[校表],[加油/压盖/测漏/真空脱脂],
							[检验],[包装]
                            FROM
                            (
                            SELECT
   
                                -- 根据时间段分组
                                CASE
                                    WHEN DATEPART(HOUR, 报工时间) < 8 THEN '00:00-08:00'
                                    WHEN DATEPART(HOUR, 报工时间) >= 20 THEN '20:00-24:00'
                                    ELSE CONCAT(FORMAT(DATEPART(HOUR, 报工时间), '00'), ':00-', FORMAT(DATEPART(HOUR, 报工时间) + 1, '00'), ':00')
                                END AS 时间段,
	                             加工顺序,工序名称,
                                不合格数量
                            FROM Z_baogong
                            WHERE 1=1 and CAST(报工时间 AS DATE) = @TargetDate ";
            if (string.IsNullOrEmpty(textBox1.Text.Trim()) == false)
            {
                sql += $" and 报工人员 like '%{textBox1.Text.Trim()}%'";
            }

            sql += @" ) AS SourceTable
                            PIVOT
                            (
                                SUM(不合格数量)
                                FOR 工序名称 IN ([焊接],[保压/清洗],[激光焊],[装配],[温度计制作],[真空充液],[校表],[加油/压盖/测漏/真空脱脂],
							[检验],[包装])
                            ) AS PivotTable) t
							group by 时间段
                            ORDER BY 时间段;
                            ";

            List<Class工序时间段> cl = SqlSugarHelp.scope.Ado.SqlQuery<Class工序时间段>(sql);
            string[] arr = new string[] { "00:00-08:00", "08:00-09:00","09:00-10:00","10:00-11:00", "11:00-12:00", "12:00-13:00",
                "13:00-14:00","14:00-15:00","15:00-16:00"
                                        ,"16:00-17:00","17:00-18:00","18:00-19:00","19:00-20:00","20:00-24:00"};
            List<Class工序时间段> ora = new List<Class工序时间段>();
            for (int i = 0; i < arr.Length; i++)
            {
                Class工序时间段 c = new Class工序时间段 { 时间段 = arr[i] };
                ora.Add(c);
            }

            foreach (var item1 in cl)
            {
                var item2 = ora.FirstOrDefault(i2 => i2.时间段 == item1.时间段);
                if (item2 != null)
                {
                    // 使用Mapster映射除TimePeriod以外的所有字段
                    item1.Adapt(item2);
                    item2.小计 = item2.焊接 + item2.保压 + item2.加油 + item2.激光焊 + item2.装配 + item2.温度计制作 + item2.真空充液 + item2.校表
                        + item2.检验 + item2.包装;
                }
            }


            Class工序时间段 c2 = new Class工序时间段
            {
                时间段 = "汇总",
                焊接 = ora.Sum(it => it.焊接),
                保压 = ora.Sum(it => it.保压),
                加油 = ora.Sum(it => it.加油),
                激光焊 = ora.Sum(it => it.激光焊),
                装配 = ora.Sum(it => it.装配),
                温度计制作 = ora.Sum(it => it.温度计制作),
                真空充液 = ora.Sum(it => it.真空充液),
                校表 = ora.Sum(it => it.校表),
                检验 = ora.Sum(it => it.检验),
                包装 = ora.Sum(it => it.包装),
                小计 = ora.Sum(it => it.小计)
            };
            ora.Add(c2);

            //cl.Adapt(ora);
            dataGridView1.Columns.Clear();
            bs.DataSource = ora;
            dataGridView1.DataSource = bs;
            bs.ResetBindings(true);
        }

        private void button2_Click(object sender, EventArgs e)
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog();
            saveFileDialog.Filter = "Excel文件(*.xlsx)|*.xlsx";
            saveFileDialog.FileName = $"工序时间段报表{dateTimePicker1.Value.ToString("yyMMdd")}";
            if (saveFileDialog.ShowDialog() == DialogResult.OK)
            {
                MiniExcel.SaveAs(saveFileDialog.FileName, bs, overwriteFile: true);
                MessageBox.Show("导出成功");
            }
        }

        private void Form工序时间段报表_Load(object sender, EventArgs e)
        {
            comboBox1.SelectedIndex = 0;
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.SelectedIndex == 0)
            {
                查正常报工();
            }
            else
            {
                查不良报工();
            }
        }
    }
}
